import os
import pandas as pd
from flask import Flask, request, jsonify
import numpy as np
import numpy_financial as npf
from sqlalchemy.dialects.mysql import VARCHAR, DECIMAL
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, text
from sqlalchemy.dialects.mysql import DOUBLE
from sqlalchemy.exc import SQLAlchemyError
import uuid
import decimal
decimal.getcontext().prec = 10  # 设置高精度
decimal.getcontext().rounding = decimal.ROUND_HALF_UP  # 使用与Excel一致的四舍五入规则
username = 'root'
password = '123456'
host = '192.168.0.189'
port = '3306'
database = 'shale-gas'
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}')

def load_gas_production_and_investment_data():
    """
    从数据库加载气井各年份产量数据和建设期投资，并转换为亿方单位（除以 10000）。
    建设期的建设投资使用 total_cost 值，其余年份设定为 0。
    如果未找到数据或出现数据库错误，将返回包含 None 的默认列表和 0 的建设投资数据。
    """
    query = """
        SELECT 
            gas_production_year1, gas_production_year2, gas_production_year3, gas_production_year4, gas_production_year5,
            gas_production_year6, gas_production_year7, gas_production_year8, gas_production_year9, gas_production_year10,
            gas_production_year11, gas_production_year12, gas_production_year13, gas_production_year14, gas_production_year15,
            gas_production_year16, gas_production_year17, gas_production_year18, gas_production_year19, gas_production_year20,
            total_cost
        FROM gas_well_para_optimization LIMIT 1
    """

    try:
        with engine.connect() as conn:
            result = conn.execute(text(query))
            row = result.fetchone()

        if row:
            # 读取各年产量数据并转换为亿方单位（除以 10000）
            production_data = [value / 10000 if value is not None else None for value in row[:-1]]
            # 读取建设期投资
            construction_investment = row[-1] if row[-1] is not None else 0
            # 其余年份建设投资设为 0
            investment_data = [construction_investment] + [0] * 20

            return production_data, investment_data

        # 未查询到任何数据的情况
        print("No production or investment data found in gas_well_para_optimization.")
        return [None] * 20, [0] * 21

    except SQLAlchemyError as e:
        # 数据库连接或查询出现错误
        print(f"Database error while loading production and investment data: {e}")
        return [None] * 20, [0] * 21

default_data = {
    "项目指标": [
        "产量（亿方）", "商品率（%）", "商品量（亿方）", "价格（元/千方）", "补贴（元/千方）",
        "城建税、教育附加费率", "矿产资源税率（%）", "矿产资源补偿率（%）",
        "操作费（元/千方）", "管理费（元/千方）", "销售费率", "所得税率",
        "营运期投资", "流动资金", "增值税率", "增值系数",
        "安全费用系数（元/千方）", "建设投资", "财务费用（弃置成本财务费用）", "弃置费", "销售费"
    ],
    "第1年": [5.5149, 0.96, 5.29, 1170, 200, 0.12, 0, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 163964, 0, 0, 0],
    "第2年": [9.6081, 0.96, 9.22, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第3年": [3.6296, 0.96, 3.48, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第4年": [2.2372, 0.96, 2.15, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第5年": [1.7147, 0.96, 1.65, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第6年": [1.4523, 0.96, 1.39, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第7年": [1.2888, 0.96, 1.24, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第8年": [1.1564, 0.96, 1.11, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第9年": [1.0492, 0.96, 1.01, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第10年": [0.9625, 0.96, 0.92, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第11年": [0.8927, 0.96, 0.86, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第12年": [0.8371, 0.96, 0.80, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第13年": [0.7935, 0.96, 0.76, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第14年": [0.7521, 0.96, 0.72, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第15年": [0.7128, 0.96, 0.68, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第16年": [0.6754, 0.96, 0.65, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第17年": [0.6399, 0.96, 0.61, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第18年": [0.6062, 0.96, 0.58, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第19年": [0.5741, 0.96, 0.55, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第20年": [0.5437, 0.96, 0.52, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第21年": [0.5437, 0.96, 0.52, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
}

def calculate_payback_period(output_data):
    # 动态获取从“第1年”到最大年份的列范围
    year_columns = output_data.columns[output_data.columns.get_loc("第1年"):]

    # 提取累计税后净现金流量和所得税后净现金流数据
    cumulative_cash_flows = output_data.loc[output_data["项目指标"] == "累计税后净现金流量", year_columns].values.flatten()
    annual_cash_flows = output_data.loc[output_data["项目指标"] == "所得税后净现金流", year_columns].values.flatten()

    # 计算负现金流的期数
    negative_periods = sum(cf < 0 for cf in cumulative_cash_flows)

    # 如果没有正现金流转正的年份，则返回“大于最大年份”
    if negative_periods >= len(annual_cash_flows):
        return "大于最大年份"

    # 获取最后一个负的累计现金流的绝对值
    last_negative_value = abs(cumulative_cash_flows[negative_periods - 1])

    # 获取转正后的第一个正现金流的绝对值
    first_positive_value = abs(annual_cash_flows[negative_periods])

    # 计算回收期
    payback_period = negative_periods + (last_negative_value / first_positive_value)

    return payback_period

def calculate_total_npv(output_data, discount_rate=0.06):
    # 提取年份列并排序
    year_columns = [col for col in output_data.columns if col.startswith("第") and col.endswith("年")]
    cash_flows = output_data.loc[output_data["项目指标"] == "所得税后净现金流", year_columns].values.flatten()
    # 计算净现值 (NPV)
    npv = sum(cf / (1 + discount_rate) ** i for i, cf in enumerate(cash_flows, 1))
    return npv


def calculate_irr(output_data):
    # Extract year columns that contain cash flows
    year_columns = [col for col in output_data.columns if col.startswith("第") and col.endswith("年")]

    # Get cash flow values for "所得税后净现金流" and flatten into a 1D array
    cash_flows = output_data.loc[output_data["项目指标"] == "所得税后净现金流", year_columns].values.flatten()

    # Filter out non-numeric values (e.g., strings, None) before removing NaNs and infinite values
    cash_flows = np.array([cf for cf in cash_flows if isinstance(cf, (int, float))])

    # Remove any NaN or infinite values from cash flows to avoid errors in IRR calculation
    cash_flows = cash_flows[~np.isnan(cash_flows) & np.isfinite(cash_flows)]

    # Ensure cash flows are valid for IRR calculation
    if cash_flows.size == 0 or np.all(cash_flows == 0):
        raise ValueError("Cash flow data is empty or contains only zero values, which is invalid for IRR calculation.")

    # Calculate IRR using numpy_financial
    irr = npf.irr(cash_flows)
    return irr


def calculate_output(df):
    try:
        years = df.columns[1:]  # 获取年份列
        # Initialize output_data structure with all required indicators
        output_data = {
            "项目指标": [
                "现金流入", "产品营业收入", "回收流动资金", "其它收入（补贴）", "现金流出", "建设投资", "经营成本费用",
                "二级单位管理费", "操作成本", "其他成本（安全生产费）", "营业税金及附加", "增值税", "城建税、教育附加费",
                "矿产资源补偿费", "矿产资源税", "利润总额（不含所得税）", "所得税", "净利润", "所得税后净现金流",
                "累计税后净现金流量", "投资折旧", "销售费用"
            ]
        }

        # Determine the maximum year dynamically based on the columns
        last_year = max(int(col[1:-1]) for col in years if col.startswith("第") and col.endswith("年"))

        # Initialize each year's data in output_data
        for year in [f"第{i}年" for i in range(1, last_year + 1)]:
            output_data[year] = [None] * len(output_data["项目指标"])

        cumulative_net_cash_flow = 0
        total_investment = df.loc[df["项目指标"] == "建设投资", years].sum(axis=1).values[0]
        cumulative_depreciation = 0

        # Populate data for each year
        for i, year in enumerate(years):
            # 商品量（亿方） = 产量（亿方） * 商品率（%）
            production_volume = df.loc[df["项目指标"] == "产量（亿方）", year].values[0]
            product_rate = df.loc[df["项目指标"] == "商品率（%）", year].values[0]
            product_volume = production_volume * product_rate

            # 产品营业收入 = 商品量（亿方） * 价格（元/千方） * 10
            price = df.loc[df["项目指标"] == "价格（元/千方）", year].values[0]
            product_revenue = product_volume * price * 10
            output_data[year][output_data["项目指标"].index("产品营业收入")] = product_revenue

            # 其它收入（补贴）= 商品量（亿方） * 补贴（元/千方） * 10
            subsidy_price = df.loc[df["项目指标"] == "补贴（元/千方）", year].values[0]
            subsidy = subsidy_price * product_volume * 10
            output_data[year][output_data["项目指标"].index("其它收入（补贴）")] = subsidy

            # 回收流动资金
            recovered_capital = df.loc[df["项目指标"] == "回收流动资金", year].values[0] if "回收流动资金" in df["项目指标"].values else 0
            output_data[year][output_data["项目指标"].index("回收流动资金")] = recovered_capital

            # 现金流入 = 产品营业收入 + 其它收入（补贴）+ 回收流动资金
            cash_inflow = product_revenue + subsidy + recovered_capital
            output_data[year][output_data["项目指标"].index("现金流入")] = cash_inflow

            # 建设投资
            construction_investment = df.loc[df["项目指标"] == "建设投资", year].values[0]
            output_data[year][output_data["项目指标"].index("建设投资")] = construction_investment

            # Calculate various costs
            management_price = df.loc[df["项目指标"] == "管理费（元/千方）", year].values[0]
            management_cost = production_volume * management_price * 10
            sale_cost = df.loc[df["项目指标"] == "销售费", year].values[0]
            operating_cost = product_volume * df.loc[df["项目指标"] == "操作费（元/千方）", year].values[0] * 10
            safety_cost = df.loc[df["项目指标"] == "安全费用系数（元/千方）", year].values[0] * production_volume * 10
            total_operating_costs = management_cost + sale_cost + operating_cost + safety_cost

            # Assign values to output_data
            output_data[year][output_data["项目指标"].index("经营成本费用")] = total_operating_costs
            output_data[year][output_data["项目指标"].index("二级单位管理费")] = management_cost
            output_data[year][output_data["项目指标"].index("销售费用")] = sale_cost
            output_data[year][output_data["项目指标"].index("操作成本")] = operating_cost
            output_data[year][output_data["项目指标"].index("其他成本（安全生产费）")] = safety_cost

            # Calculate taxes and surcharges
            value_added_tax = cash_inflow * df.loc[df["项目指标"] == "增值税率", year].values[0] * df.loc[df["项目指标"] == "增值系数", year].values[0]
            urban_education_surcharge = value_added_tax * df.loc[df["项目指标"] == "城建税、教育附加费率", year].values[0]
            resource_compensation_fee = product_revenue * df.loc[df["项目指标"] == "矿产资源补偿率（%）", year].values[0]
            resource_tax = product_revenue * df.loc[df["项目指标"] == "矿产资源税率（%）", year].values[0]
            business_tax_surcharge = urban_education_surcharge + resource_compensation_fee + resource_tax

            # 营业税金及附加计算：（1）增值税，（2）城建税、教育附加费，（3）矿产资源补偿费，（4）矿产资源税
            # （1）增值税 = 现金流入 * 增值税率 * 增值税系数
            value_added_tax = cash_inflow * df.loc[df["项目指标"] == "增值税率", year].values[0] * \
                              df.loc[df["项目指标"] == "增值系数", year].values[0]
            # （2）城建税、教育附加费 = 增值税 * 城建税、教育附加费率
            urban_education_surcharge = value_added_tax * df.loc[df["项目指标"] == "城建税、教育附加费率", year].values[
                0]
            # （3）矿产资源补偿费 = 产品营业收入 * 矿产资源补偿率（%）
            resource_compensation_fee = product_revenue * df.loc[df["项目指标"] == "矿产资源补偿率（%）", year].values[0]
            # （4）矿产资源税
            resource_tax = product_revenue * df.loc[df["项目指标"] == "矿产资源税率（%）", year].values[0]
            business_tax_surcharge = urban_education_surcharge + resource_compensation_fee + resource_tax
            output_data[year][output_data["项目指标"].index("营业税金及附加")] = business_tax_surcharge
            output_data[year][output_data["项目指标"].index("增值税")] = value_added_tax
            output_data[year][output_data["项目指标"].index("城建税、教育附加费")] = urban_education_surcharge
            output_data[year][output_data["项目指标"].index("矿产资源补偿费")] = resource_compensation_fee
            output_data[year][output_data["项目指标"].index("矿产资源税")] = resource_tax

            # Assign tax values to output_data
            output_data[year][output_data["项目指标"].index("营业税金及附加")] = business_tax_surcharge
            output_data[year][output_data["项目指标"].index("增值税")] = value_added_tax
            output_data[year][output_data["项目指标"].index("城建税、教育附加费")] = urban_education_surcharge
            output_data[year][output_data["项目指标"].index("矿产资源补偿费")] = resource_compensation_fee
            output_data[year][output_data["项目指标"].index("矿产资源税")] = resource_tax

            # 现金流出
            print(df[df["项目指标"] == "营运期投资"].empty)
            print(df.loc[df["项目指标"] == "营运期投资", year].values[0])
            cash_outflow = (construction_investment + df.loc[df["项目指标"] == "营运期投资", year].values[0] + total_operating_costs
                            + business_tax_surcharge + df.loc[df["项目指标"] == "财务费用（弃置成本财务费用）", year].values[0]
                            + df.loc[df["项目指标"] == "弃置费", year].values[0])
            output_data[year][output_data["项目指标"].index("现金流出")] = cash_outflow

            # 折旧和累计现金流
            if df.loc[df["项目指标"] == "产量（亿方）", year].values[0] == 0:
                depreciation = 0
            else:
                remaining_gas_production = sum(df.loc[df['项目指标'] == '产量（亿方）', y].values[0] for y in years[years.get_loc(year):])
                depreciation = ((total_investment - cumulative_depreciation) *
                                (df.loc[df['项目指标'] == '产量（亿方）', year].values[0] / remaining_gas_production)) if remaining_gas_production > 0 else 0
                cumulative_depreciation += depreciation

            output_data[year][output_data["项目指标"].index("投资折旧")] = int(depreciation)

            # Calculate profit and taxes
            profit_total = cash_inflow - (total_operating_costs + business_tax_surcharge + depreciation +
                                          df.loc[df["项目指标"] == "财务费用（弃置成本财务费用）", year].values[0] +
                                          df.loc[df["项目指标"] == "弃置费", year].values[0])
            income_tax = profit_total * df.loc[df["项目指标"] == "所得税率", year].values[0] if profit_total > 0 else 0
            net_profit = profit_total - income_tax
            net_cash_flow_after_tax = cash_inflow - cash_outflow - income_tax
            cumulative_net_cash_flow += net_cash_flow_after_tax

            # Store results in output_data
            output_data[year][output_data["项目指标"].index("利润总额（不含所得税）")] = profit_total
            output_data[year][output_data["项目指标"].index("所得税")] = income_tax
            output_data[year][output_data["项目指标"].index("净利润")] = net_profit
            print(cash_inflow - cash_outflow - income_tax)
            output_data[year][output_data["项目指标"].index("所得税后净现金流")] = net_cash_flow_after_tax
            # Store cumulative net cash flow result
            output_data[year][output_data["项目指标"].index("累计税后净现金流量")] = cumulative_net_cash_flow

        # Convert output_data to a DataFrame
        res_data = pd.DataFrame(output_data)
        print(res_data)
        # Calculate financial indicators
        total_npv = calculate_total_npv(res_data)
        internal_rate_of_return = calculate_irr(res_data)
        payback_period = calculate_payback_period(res_data)

        return res_data, total_npv, internal_rate_of_return, payback_period

    except Exception as e:
        raise ValueError(f"Error in calculate_output: {str(e)}")
